﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NUMIMAX
{
    public class DatabaseQuerries
    {
        private static string createCoinsTable = @"CREATE TABLE coins(
                                                 id_coin INTEGER PRIMARY KEY,
                                                 id_cat INTEGER,
                                                 id_sub INTEGER,
                                                 name VARCHAR(80),
                                                 year_em VARCHAR(8),
                                                 denomin INTEGER,
                                                 edition VARCHAR(11),
                                                 stamp VARCHAR(4),
                                                 alloy VARCHAR(6),
                                                 weight VARCHAR(8),
                                                 diameter VARCHAR(10),
                                                 rant VARCHAR(9),
                                                 nb_catalogue VARCHAR(10),
                                                 index1 VARCHAR(6), 
                                                 index2 VARCHAR(6),
                                                 price_issue REAL,
                                                 status VARCHAR(12),
                                                 id_coin_img INTEGER,
                                                 id_coin_price INTEGER,
                                                 id_coin_price_quarter INTEGER,
                                                 id_coin_type INTEGER,
                                                 rec_status VARCHAR(1),
                                                 currency VARCHAR(30),
                                                 country VARCHAR(30))";

        private static string createConfigTable = @"CREATE TABLE config(
                                                  nb_ver INTEGER,
                                                  date_ver DATE,
                                                  sort_way INTEGER,
                                                  sort_field INTEGER,
                                                  app_wnd_x INTEGER,
                                                  app_wnd_y INTEGER,
                                                  app_wnd_sx INTEGER,
                                                  app_wnd_sy INTEGER,
                                                  tree_wnd_sx INTEGER)";

        private static string createCategoryTable = @"CREATE TABLE category(
                                                    id_cat INTEGER PRIMARY KEY,
                                                    name VARCHAR(25),
                                                    rec_status VARCHAR(1))";
        
        private static string createSubcategoryTable = @"CREATE TABLE subcat(
                                                       id_sub INTEGER PRIMARY KEY,
                                                       id_cat INTEGER,
                                                       name VARCHAR(25),
                                                       rec_status VARCHAR(1))";

        private static string createCoinsImageTable = @"CREATE TABLE coins_img(
                                                      id_coin_img INTEGER PRIMARY KEY,
                                                      img BLOB,
                                                      rec_status VARCHAR(1))";

        private static string createCoinsPricesTable = @"CREATE TABLE coins_prices(
                                                       id_coin_price INTEGER PRIMARY KEY,
                                                       date_price_current DATE,
                                                       price_current REAL,
                                                       date_price_prev_1 DATE,
                                                       price_prev_1 REAL,
                                                       date_price_prev_2 DATE,
                                                       price_prev_2 REAL,
                                                       date_price_prev_3 DATE,
                                                       price_prev_3 REAL,
                                                       date_price_prev_4 DATE,
                                                       price_prev_4 REAL,
                                                       date_price_prev_5 DATE,
                                                       price_prev_5 REAL,
                                                       rec_status VARCHAR(1))";

        private static string createCoinsPricesQuarterTable = @"CREATE TABLE coins_prices_quarter(
                                                              id_coin_price_quarter INTEGER PRIMARY KEY,
                                                              period_1 VARCHAR(10),
                                                              price_period_1 REAL,
                                                              period_2 VARCHAR(10),
                                                              price_period_2 REAL,
                                                              period_3 VARCHAR(10),
                                                              price_period_3 REAL,
                                                              period_4 VARCHAR(10),
                                                              price_period_4 REAL,
                                                              period_5 VARCHAR(10),
                                                              price_period_5 REAL,
                                                              period_6 VARCHAR(10),
                                                              price_period_6 REAL,
                                                              rec_status VARCHAR(1))";

        private static string createCoinsPricesYearTable = @"CREATE TABLE coins_prices_year(
                                                           id_coin_price_year INTEGER PRIMARY KEY,
                                                           id_coin INTEGER,
                                                           year VARCHAR(4),
                                                           price REAL,
                                                           rec_status VARCHAR(1))";

        private static string createCoinNotesTable = @"CREATE TABLE coins_notes(
                                                     id_coin_notes INTEGER PRIMARY KEY,
                                                     id_coin INTEGER,
                                                     notes TEXT)";

        private static string createCoinsItemTable = @"CREATE TABLE coins_item(
                                                     id_coin_item INTEGER PRIMARY KEY,
                                                     id_coin INTEGER,
                                                     price_purch REAL,
                                                     date_purch DATE,
                                                     state INTEGER,
                                                     status INTEGER,
                                                     is_sold VARCHAR(1),
                                                     price_sold REAL,
                                                     date_sold DATE,
                                                     qty INTEGER DEFAULT 1)";

        private static string createCoinTypeConvTable = @"CREATE TABLE coins_type_conv(
                                                        id_coin_type_conv INTEGER PRIMARY KEY,
                                                        id_coin_type INTEGER,
                                                        state_1 REAL,
                                                        state_2 REAL,
                                                        state_3 REAL,
                                                        state_4 REAL,
                                                        state_5 REAL,
                                                        state_6 REAL,
                                                        state_7 REAL)";

        private static string createStatusTable = @"CREATE TABLE status(
                                                  id_status INTEGER PRIMARY KEY,
                                                  value VARCHAR(20))";

        private static string createStateTable = @"CREATE TABLE state(
                                                 id_state INTEGER PRIMARY KEY,
                                                 value VARCHAR(10))";

        private static string createCoinsTypeTable = @"CREATE TABLE coins_type(
                                                     id_coin_type INTEGER PRIMARY KEY,
                                                     value VARCHAR(20))";

        public static string CreateCoinsTableQuerry
        {
            get { return createCoinsTable; }
        }

        public static string CreateConfigTableQuerry
        {
            get { return createConfigTable; }
        }

        public static string CreateCategoryTableQuerry
        {
            get { return createCategoryTable; }
        }
       
        public static string CreateSubcategoryTableQuerry
        {
            get { return createSubcategoryTable; }
        }

        public static string CreateCoinsImageTableQuerry
        {
            get { return createCoinsImageTable; }
        }

        public static string CreateCoinsPricesTableQuerry
        {
            get { return createCoinsPricesTable; }
        }

        public static string CreateCoinsPricesQuarterTableQuerry
        {
            get { return createCoinsPricesQuarterTable; }
        }

        public static string CreateCoinsPricesYearTableQuerry
        {
            get { return createCoinsPricesYearTable; }
        }

        public static string CreateCoinNotesTableQuerry
        {
            get { return createCoinNotesTable; }
        }
        
        public static string CreateCoinsItemTableQuerry
        {
            get { return createCoinsItemTable; }
        }

        public static string CreateCoinsTypeConvTableQuerry
        {
            get { return createCoinTypeConvTable; }
        }

        public static string CreateStatusTableQuerry
        {
            get { return createStatusTable; }
        }

        public static string CreateStateTableQuerry
        {
            get { return createStateTable; }
        }

        public static string CreateCoinsTypeTableQuerry
        {
            get { return createCoinsTypeTable; }
        }
    }
}
